package com.rimi.project.util;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Date;
import java.util.Properties;

import javax.sql.rowset.CachedRowSet;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.sun.rowset.CachedRowSetImpl;


public class JDBCutil {
	public static DruidDataSource dataSource ; 
	
	static {
		InputStream in = JDBCutil.class.getClassLoader().getResourceAsStream("druid.properties");
		
		Properties p = new Properties();
		
		try {
			p.load(in);
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		
		try {
			dataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(p);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static int update(String sql) {
		// 第二步、建立连接
		Connection connection = getConnection();
		// 第三步、建立通道
		Statement statement = getStatement(connection);
		try {
			
			// 第四步，row受影响的行数
			int row = statement.executeUpdate(sql);
			
			return row;
			
		} catch (SQLException e) {

			e.printStackTrace();
		} finally {
			closes(null,statement,connection);
		}
		
		return -1;
	}


	public static ResultSet query(String sql) {
		Connection connection = getConnection();
		Statement statement = getStatement(connection);
		ResultSet resultSet = null;
		CachedRowSet cachedRowSet = null;
		try {
			cachedRowSet = new CachedRowSetImpl();
			resultSet = statement.executeQuery(sql);
			
			cachedRowSet.populate(resultSet);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closes(resultSet, statement,connection);
		}
		
		return cachedRowSet;
	}
	
	public static int preUpdate(String sql,Object[] str) {
		// 建立连接
		Connection connection = getConnection();
		
		// 建立pre通道
		PreparedStatement ps = getPreparedStatement(sql,connection);
		 
		// 参数绑定
		bandle(ps,str);
		
		// 执行sql
		int row = -1;
		try {
			row = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closes(null,ps,connection);
		}
		return row;
	}
	
	public static ResultSet preQuery(String sql,Object[] str) {
		Connection connection = getConnection();
		
		PreparedStatement preparedStatement = getPreparedStatement(sql, connection);
		
		bandle(preparedStatement, str);
		
		ResultSet resultSet = null;
		CachedRowSet cachedRowSet = null;
		try {
			cachedRowSet = new CachedRowSetImpl();
			
			resultSet = preparedStatement.executeQuery();
			
			cachedRowSet.populate(resultSet);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closes(resultSet, preparedStatement, connection);
		}
		return cachedRowSet;	
	}
	
	// 关闭数据库资源
	private static void closes(ResultSet resultSet, Statement ps, Connection connection) {
		try {
			if (resultSet != null) {
				resultSet.close();
			}
			if (ps != null) {
				ps.close();
			}
			if (connection != null) {
				connection.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	// 封装参数绑定
	private static void bandle(PreparedStatement ps, Object[] str) {
		if (str != null) {
			for (int index = 1; index <= str.length; index++) {
				try {
					if (str[index-1] instanceof String) {
						ps.setString(index, (String) str[index-1]);
					} else if (str[index-1] instanceof Integer) {
						ps.setInt(index, (int) str[index-1]);
					} else if (str[index-1] instanceof Double) {
						ps.setDouble(index, (double) str[index-1]);
					} else if (str[index-1] instanceof java.util.Date) {
						Date date = (Date)str[index-1];
						ps.setTimestamp(index, new Timestamp(date.getTime()));
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}
	
	// 封装statement通道
	private static Statement getStatement(Connection connection) {
		Statement statement = null;
		try {
			statement = connection.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return statement;
	}
	
	// 封装pre通道
	private static PreparedStatement getPreparedStatement(String sql, Connection connection) {
		PreparedStatement preparedStatement = null;
		try {
			preparedStatement = connection.prepareStatement(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return preparedStatement;
	}

	// 封装连接
	private static Connection getConnection() {
		Connection connection = null;
		try {
			connection = dataSource.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return connection;
	}
}
